﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public class MessageSendBLL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        public DataTable GetCurrentApprover(int emp_seq, string process, int step_no)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@process", process);
                dbManager.AddParameters(2, "@step_no", step_no);
                DataTable returnData = new DataTable();

                string sql = "select approve_path_seq, approver_type, exec_pos_group_seq, pos_seq, null as emp_seq"
                    + " from sys_approve_path path"
                    + " where process = @process"
                    + " and step_no = @step_no";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                bool bFound = false;
                DataTable dtb2 = new DataTable();

                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["approver_type"].ToString() == "1")
                    {
                        
                        int level = 1;

                        while (!bFound && level <= 5)
                        {
                            string strForm = "";

                            for (int i = 0; i < level; i++)
                            {
                                strForm = strForm + " left join hrm_mp_positions pos@1 on pos@2.working_upper_pos_seq = pos@1.pos_seq".Replace("@1", (i + 1).ToString()).Replace("@2", i.ToString());

                            }

                            strForm = strForm + " left join sys_exec_pos_group_dtls grp on pos@1.exec_pos_seq = grp.exec_pos_seq".Replace("@1", level.ToString());

                            sql = ("select emp2.emp_seq, pos@2.pos_seq, grp.exec_pos_group_seq"

                                + " from hrm_ps_employees emp1"
                                + " inner join hrm_mp_positions pos0 on emp1.pos_seq = pos0.pos_seq"
                                + strForm
                                + " left join hrm_ps_employees emp2 on pos@2.pos_seq = emp2.pos_seq"
                                    + " and emp2.record_status not like 'D'"
                                + " where emp1.emp_seq = @emp_seq").Replace("@2", level.ToString());
                            dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                            if (dtb2.Rows.Count > 0)
                            {
                                bFound = dtb2.Rows[0]["exec_pos_group_seq"].ToString() == dt.Rows[0]["exec_pos_group_seq"].ToString();
                                returnData = dtb2;
                            }
                            else
                            {
                                returnData = null;
                            }

                            level++;
                        }
                    }
                    else
                    {
                        sql = "select emp.emp_seq"
                            + ", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq"
                            + " from hrm_ps_employees emp"
                            + " left join hrm_ps_employee_statuses es on emp.emp_status_seq = es.emp_status_seq"
                            + " where emp.pos_seq = " + dt.Rows[0]["pos_seq"].ToString()
                            + " and es.active = '1'";
                        dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                        returnData = dtb2;
                        bFound = true;
                    }
                }

                if (!bFound)
                {
                    returnData = dt;
                }

                return returnData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(MessageSendInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@send_date", info.send_date);
                dbManager.AddParameters(2, "@ref_seq", info.ref_seq);
                dbManager.AddParameters(3, "@ref_type", info.ref_type);
                dbManager.AddParameters(4, "@ref_no", info.ref_no);
                dbManager.AddParameters(5, "@send_text", info.send_text);
                dbManager.AddParameters(6, "@from_emp_seq", info.from_emp_seq);
                dbManager.AddParameters(7, "@to_emp_seq", info.to_emp_seq);
                dbManager.AddParameters(8, "@from_send_seq", info.from_send_seq);
                dbManager.AddParameters(9, "@remarks", info.remarks);
                dbManager.AddParameters(10, "@message_status", info.message_status);
                dbManager.AddParameters(11, "@approve_path_seq", info.approve_path_seq);

                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@created_by", info.created_by);
                dbManager.AddParameters(14, "@created_when", info.created_when);

                dbManager.AddParameters(15, "@from_pos_seq", info.from_pos_seq);
                dbManager.AddParameters(16, "@to_pos_seq", info.to_pos_seq);
                dbManager.AddParameters(17, "@wod_emp_seq", info.wod_emp_seq);

                strSQL = "INSERT INTO message_send"
                    + "(send_date, ref_seq, ref_type, ref_no, send_text"
                    + ", from_emp_seq, to_emp_seq, from_send_seq, remarks"
                    + ", message_status, approve_path_seq"
                    + ", record_status, created_by, created_when"
                    + ", from_pos_seq, to_pos_seq, wod_emp_seq)"
                    + " VALUES(@send_date"
                        + ", @ref_seq"
                        + ", @ref_type"
                        + ", @ref_no"
                        + ", @send_text"
                        + ", @from_emp_seq"
                        + ", @to_emp_seq"
                        + ", @from_send_seq"
                        + ", @remarks"
                        + ", @message_status"
                        + ", @approve_path_seq"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"

                        + ", @from_pos_seq"
                        + ", @to_pos_seq"
                        + ", @wod_emp_seq"
                        + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public int? GetPosSeqFromEmpSeq(int? nempSeq)
        {
            string strSQL;
            DataRowCollection drc = null;
            MessageSendInfo info = new MessageSendInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", nempSeq);

                strSQL =
                    @"select pos_seq from hrm_ps_employees where record_status != @delete_status and emp_seq = @emp_seq;";

                drc = dbManager.ExecuteDataTable(CommandType.Text, strSQL).Rows;
                if (drc.Count > 0)
                {
                    return drc[0]["pos_seq"] == DBNull.Value ? (int?)null : Convert.ToInt32(drc[0]["pos_seq"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return null;
        }
    }
}
